﻿Imports System.Text

Public Class DataAccess
    Inherits BrainworkLite.DataAccess.DataAccessBase

    Public Function getTables() As DataSet
        Try
            Me.Open()
            Return ExecuteSQLGetDataSet("SELECT SO.NAME FROM sys.objects SO  WHERE SO.TYPE = 'U' and SO.NAME <> 'sysdiagrams' ORDER BY SO.NAME")

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

    Public Function getEmptyDataSet(ByVal TableName) As DataSet
        Try
            Me.Open()
            Dim StrSql As String = "Select * From " & TableName & " where 1=0 "
            Return ExecuteSQLGetDataSet(StrSql)

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

    Public Function getFields(ByVal TableName As String) As DataTable
        Try
            Me.Open()
            Dim sb As New StringBuilder()
            sb.Append(" SELECT DISTINCT  ")
            sb.Append(" clmns.name AS [Name], ")
            sb.Append(" usrt.name AS [DataType], ")
            sb.Append(" ISNULL(baset.name, N'') AS [SystemType], ")
            sb.Append(" CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN ")
            sb.Append(" clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length], ")
            sb.Append(" CAST(clmns.precision AS int) AS [NumericPrecision] , ")
            sb.Append(" clmns.is_nullable ")
            sb.Append(" ,clmns.is_identity ")
            sb.Append(" ,isq.COLUMN_DEFAULT ")
            sb.Append(" ,CASE WHEN NOT COL_NAME(fc.referenced_object_id, fc.referenced_column_id) IS NULL THEN ")
            sb.Append(" 	OBJECT_NAME (f.referenced_object_id) ")
            sb.Append(" ELSE ")
            sb.Append(" 	NULL ")
            sb.Append(" END AS ReferenceTableName ")
            sb.Append(" ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName ")
            sb.Append(",CASE WHEN clmns.name IN  ( select COL_NAME(NFC.parent_object_id, NFC.parent_column_id)  from  sys.foreign_keys AS NF inner  JOIN sys.foreign_key_columns AS NFC  ON NF.OBJECT_ID = NFC.constraint_object_id  Where  OBJECT_NAME(NF.parent_object_id) = @tablename ) AND COL_NAME(fc.referenced_object_id, fc.referenced_column_id) is NULL THEN  1 ELSE 0 END   AS [duplicated]")
            sb.Append(" FROM ")
            sb.Append(" sys.tables AS tbl ")
            sb.Append(" INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id ")
            sb.Append(" Inner Join INFORMATION_SCHEMA.COLUMNS as isq On isq.COLUMN_NAME  = clmns.name ")
            sb.Append(" LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id ")
            sb.Append(" LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id ")
            sb.Append(" LEFT OUTER JOIN sys.foreign_keys AS f On  OBJECT_NAME(f.parent_object_id) = @tablename  ")
            sb.Append(" LEFT OUTER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id AND COL_NAME(fc.parent_object_id, fc.parent_column_id) = clmns.name ")
            sb.Append(" WHERE ")
            sb.Append(" (tbl.name=@tablename and SCHEMA_NAME(tbl.schema_id)=N'dbo') ")
            sb.Append(" AND ")
            sb.Append(" (isq.TABLE_NAME=@tablename ) ")

            Dim dsFieldsInfo = ExecuteSQLGetDataSet(sb.ToString(), getParam(Of String)("@tablename", TableName))

            Dim Qe = dsFieldsInfo.Tables(0).AsEnumerable().Where(Function(x) x.Field(Of Int32)("duplicated") = 0)
            Dim ds = Qe.CopyToDataTable()



            Return ds

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

End Class
